Skip to main content
Version: 2.8

4 Utils​

4.1 evd2sql​

(since EVL 2.6)

Read the EVL data definition (a.k.a. EVD) from <table.evd> and write to standard output (unless ‘--output’ option is used) ‘CREATE TABLE’ statement specific for given SQL dialect: ANSI, MS SQL, PostgreSQL, Redshift, etc.

When more than one <table.evd> files specified, then write to ‘*.sql’ files named accordingly with the same basename.

The SQL statement looks like this in general:

CREATE TABLE [IF NOT EXISTS] ["<schema_name>".]"<table_name>" (
    <column_1_based_on_evd>
  , <column_2_based_on_evd>
  , <column_3_based_on_evd>
  , ...
[<table_constraints>]
)
[<table_attributes>]
;

EVL data types mapping:

EVLPostgres/RedshiftMS SQL
‘char’‘"CHAR"’‘SMALLINT’
‘uchar’‘BOOLEAN’‘TINYINT’
‘short’‘SMALLINT’‘SMALLINT’
‘ushort’‘SMALLSERIAL’‘INT’
‘int’‘INTEGER’‘INT’
‘uint’‘SERIAL’‘BIGINT’
‘long’‘BIGINT’‘BIGINT’
‘ulong’‘BIGSERIAL’‘DECIMAL(20,0)’
‘int128’‘NUMERIC(38,0)’‘DECIMAL(38,0)’
‘utint128’‘NUMERIC(38,0)’‘DECIMAL(38,0)’
‘float’‘REAL’‘REAL’
‘double’‘DOUBLE PRECISION’‘FLOAT’
‘decimal(m,n)’‘NUMERIC(m,n)’‘DECIMAL(m,n)’
‘string’‘TEXT’‘VARCHAR’
‘ustring’‘TEXT’‘NVARCHAR’
‘date’‘DATE’‘DATE’
‘time’‘TIME’‘TIME’
‘interval’‘INTERVAL’

N/A

‘datetime’‘TIMESTAMP(0)’‘DATETIME2(0)’
‘timestamp’‘TIMESTAMP(6)’‘DATETIME2(6)’

Synopsis​

evd2sql
  ( <table.evd>... | -i|--input <table.evd> )
  [-d|--sql-dialect <database> ]
  [--if-not-exists]
  [-o|--output ( <table.sql> | <target_dir> ) ]
  [-s|--schema <schema_name>]
  [-t|--table <table_name>]
  [--table-attributes <table_attributes>]
  [--table-constraints <table_constraints>]
  [--varchar <length>]
  [-v|--verbose]

evd2sql
  ( --help | --usage | --version )

Options​

-d, --sql-dialect=<database>
currently these SQL types are supported:

ansi (default)
mssql
postgres
redshift

--if-not-exists
use ‘CREATE TABLE IF NOT EXISTS’ instead of default ‘CREATE TABLE’

-i, --input=<table.evd>
read file <table.evd>

-o, --output=<path>
if <path> is an existing directory, it writes output there. If it is not a directory, it is considered as an output file name.

-s, --schema=<schema_name>
add <schema_name> to table name

-t, --table=<table_name>
by default basename of <table.evd> from ‘--input’ option is used as table name in ‘CREATE TABLE’ statement, this option can overwrite it. When reading EVD from standard input, this option is recommended, otherwise table name will be empty

--table-attributes=<table_attributes>
string to be added right after closing bracket, e.g. for Redshift it might be ‘SORTKEY (some_id,other_col)’

--table-constraints=<table_constraints>
string to be added right after column list, e.g. ‘, PRIMARY KEY (some_id)’

--varchar=<length>
specify the default VARCHAR length, default is 256

-v, --verbose
print to STDERR info/debug messages

--help
print this help and exit

--usage
print short usage information and exit

--version
print version and exit

Examples​

  1. Having an EVD file ‘some.evd’:

    id       int             sep=";"
    started  date    null="" sep=";"
    value    string  null="" sep="\n"

    This command:

    evd2sql -s postgres -i some.evd --if-not-exists

    will produce:

    CREATE TABLE IF NOT EXISTS "some" (
        id      INTEGER NOT NULL
      , started DATE
      , value   TEXT
    );

4.2 sql2evl​

(since EVL 2.8)

Read an SQL script and generate data definition files (a.k.a. EVD), generate mapping (EVM file) and EVL job parameters file (EVL file).

Synopsis​

sql2evl
  ( <statement.sql>... | -i|--input <statement.sql> )
  [-j|--job-name <job_name>]
  [-v|--verbose]

sql2evl
  ( --help | --usage | --version )

Options​

-i, --input=<statement.sql>
read statement from file <statement.sql>

-j, --job-name=<job_name>
by default it takes a job name from the base of the input file name. Use this option to use other name.

-v, --verbose
print to STDERR info/debug messages

--help
print this help and exit

--usage
print short usage information and exit

--version
print version and exit

Examples​

  1. Calling:

    sql2evl invoices.sql

    will produce in current directory files:

    job/invoices.evl
    evd/invoices.<unique_id>.evd
    evm/invoices.evm